/********************************************************************************
 *                                   DB                                         *
 ********************************************************************************
 *  NO ATTRIBUTES                                                               *
 ********************************************************************************
 *  boolean  createUser(String email, String password)                          *
 *  void     changelastlevel(String email, int lastlevel)                       *
 *  int      getlastlevel(String email)                                         *
 *  void     addmoney(String email, int moneydifference)                        *
 *  int      getmoney(String email)                                             *
 *  boolean  rightPassword(String email, String password)                       *
 *  void     setPassword(String email, String oldpassword, String newpassword)  *
 ********************************************************************************/
 
 /**
 *  @author Karl Sturm
 *  @version 2010/07/17
 *  
 *  This class is the connection to the database.
 *  Please do not edit or modify this class in any way unless you _really_ know what you are doing.
 *  @group: I really mean it the way i said it. Please talk to me before you make _any_ changes.
 */


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.security.*;

public class DB {

    /**
     * Creates a new user in the database.
     * The Password is transmitted and stored as MD5-Hash
     * If the User/Mailadress is already registered, only the timestamp is renewed in the DB.
     * 
     * @param  email     Email Adress of applicant
     * @param  password  New Password
     * @return           boolean successfull
     */
    public boolean createUser(String email, String password)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String pwd ="";
        boolean luck = false;
        
        try {
            //generiert double MD5-Hash
            pwd=generateMD5(generateMD5(password));
            
            //macht die verbindung zum svr auf
            conn = getConnection();
            System.out.println("conn=" + conn);
            
            //query1: "fuege einen datensatz ein, ausser die mailadresse ist schon vorhanden, dann aendere den zeitstempel"
            String query = 
            "INSERT INTO `spieler` (`mail`, `password`)" +
            "VALUES (\'" + email + "\', \'" + pwd + "\')" +
            "ON duplicate KEY UPDATE timestamp=NOW()+0;";
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            System.out.println("Queried: " + query);
            System.out.println("---------------");
            stmt.close();
            
            //query2: "gibts jetzt ein anderes passwort zur mailadresse?"
            System.out.println("conn=" + conn);
            query = "SELECT password FROM spieler WHERE mail=\'" + email +"\'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                String dbpwd = rs.getString(1);
                System.out.println("dbpwd =" + dbpwd);
                if (!pwd.equals(dbpwd))
                {
                    System.out.println("Queried: " + query);
                    System.out.println("Sorry, Mailadress already in use!");
                    System.out.println("---------------");
                    luck = false;
                }
                else 
                {
                    System.out.println("Created account " + email +" ."); 
                    luck = true;
                }
            }
            
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } 
        
        return luck;
    }

    /**
     * Changes the last level of an user in the DB.
     * 
     * @param  email      Email Adress of player
     * @param  lastlevel  Number of last played level
     */
    public void changelastlevel(String email, int lastlevel)
    {
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "UPDATE spieler SET lastlevel=" + lastlevel + " WHERE mail=\'" + email + "\';";
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            System.out.println("Queried: " + query);
            System.out.println("---------------");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    /**
     * Displays the last level of an user in the DB.
     * 
     * @param  email  Email Adress of player
     * @return        The number of the last played level
     */
    public int getlastlevel(String email)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int lastlevel = 0;
        
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "SELECT lastlevel FROM spieler WHERE mail=\'" + email +"\'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                lastlevel = rs.getInt(1);
                System.out.println("Queried: " + query);
                System.out.println("thus the last level is: " + lastlevel);
                System.out.println("---------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } 
        return lastlevel;
    }

    /**
     * Adds / subtracts the money of a player on his account
     * 
     * @param  email            Email Adress of player
     * @param  moneydifference  The money to be added/subtractet onto/from the account. Number can be positive or negative.
     */
    public void addmoney(String email, int moneydifference)
    {
        Connection conn = null;
        Statement stmt = null;
        
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "UPDATE spieler SET money=money+" + moneydifference + " WHERE mail=\'" + email + "\';";
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            System.out.println("Queried: " + query);
            System.out.println("---------------");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Shows the current amount of money on the player's account
     * 
     * @param  email  Email Adress of player
     * @return        The money currently on the  player's account
     */
    public int getmoney(String email)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int money = 0;
        
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "SELECT money FROM spieler WHERE mail=\'" + email +"\'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                money = rs.getInt(1);
                System.out.println("Queried: " + query);
                System.out.println("thus the money is: " + money);
                System.out.println("---------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } 
        return money;
    }
    
    /**
     * Proves if the entered password is the right one... or... proves if the user the user he ought to be?
     * 
     * @param  email     Email Adress of player
     * @param  password_p  the password which is to be proven as legitimite
     * @return           if the password's the right one it returns "true"
     */
    public boolean rightPassword(String email, String password_p)
    {
        String pwd = null;
        String password = password_p;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "SELECT password FROM spieler WHERE mail=\'" + email +"\'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                pwd = rs.getString(1);
                System.out.println("Queried: " + query);
                System.out.println("thus the password is: " + pwd);
                System.out.println("---------------");
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                rs.close();
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } 
        
        // check password.. is it the right one?
        try { 
            password = generateMD5(generateMD5(password)); 
        } catch (Exception e) { 
            System.out.println("MD5-fail");
        }
        if (pwd != null && pwd.equals(password))
        {
            return true;
        }
        else 
        {
            return false;
        }
    }
    
    /**
     * Changes the password of a user
     * 
     * @param  email        Email Adress of player
     * @param  oldpassword  the password which is to be proven as legitimite
     * @param  newpassword  the new password to be saved in the database
     */
    public void setPassword(String email, String oldpassword, String newpassword)
    {
        try { 
            newpassword = generateMD5(generateMD5(newpassword));
            oldpassword = generateMD5(generateMD5(oldpassword));
        } catch (Exception e) { 
            System.out.println("MD5-fail"); 
            return;
        }
        String pwd = null;
        Connection conn = null;
        Statement stmt = null;
                
        try {
            conn = getConnection();
            System.out.println("conn=" + conn);
            String query = "UPDATE spieler SET password=\'" + newpassword + "\' WHERE mail=\'" + email + "\' AND password=\'" + oldpassword + "\';";
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            System.out.println("Queried: " + query);
            System.out.println("---------------");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail");
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    private Connection getConnection() throws Exception {
        String url = "jdbc:mysql://rip-gaming.tk:3306/bank";
        String username = "q11";
        String password = "deinemudda";
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }
    
    private String generateMD5(String input) throws Exception {
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.reset();
        md5.update(input.getBytes());
        byte[] result = md5.digest();
        StringBuffer hexString = new StringBuffer();
        for (int i=0; i<result.length; i++) {
            hexString.append(String.format("%02x", result[i]));
        }
        return hexString.toString();
    }
}